
DROP TABLE IF EXISTS jms_dm.dm_sham_send_monitor_detail_dt ;

create external table jms_dm.dm_sham_send_monitor_detail_dt
(
bill_code string comment '运单编号',
input_time string comment '运单录入时间',
taking_time string comment '揽收时间',
network_code string comment '首责任网点code',
network_name string comment '首责任网点名称',
network_type string comment '首责任网点类型',
agent_code string comment '首责任机构所属代理区code',
agent_name string comment '首责任机构所属代理区',
fst_duty_type string comment '首责任类型',
fst_duty_standard string comment '首责任标准',
start_network_code string comment '始发网点code',
start_network_name string comment '始发网点名称',
start_agent_code string comment '始发代理区code',
start_agent_name string comment '始发代理区',
start_city_id string comment '始发城市id',
start_city_desc string comment '始发城市',
start_provider_id string comment '始发省份id',
start_provider_desc string comment '始发省份',
destination_network_code string comment '目的网点code',
destination_network_name string comment '目的网点',
destination_agent_code string comment '目的代理区code',
destination_agent_name string comment '目的代理区',
destination_city_id string comment '目的城市id',
destination_city_desc string comment '目的城市',
destination_provider_id string comment '目的省份id',
destination_provider_desc string comment '目的省份',
line_link_code string comment '链路环节code',
line_link_name string comment '链路环节',
duty_org_code string comment '责任机构code',
duty_org_name string comment '责任机构',
duration_hour string comment '时长',
deliver_network_code string comment '出仓网点code',
deliver_network_name string comment '出仓网点',
deliver_city_id string comment '出仓网点所属城市id',
deliver_city_desc string comment '出仓网点所属城市',
deliver_provider_id string comment '出仓网点所属省份id',
deliver_provider_desc string comment '出仓网点所属省份',
sign_time date comment '签收时间',
is_reback string comment '是否退件'
)
COMMENT '虚假发货监控-明细表'
PARTITIONED BY ( dt STRING COMMENT '分区日期')
STORED AS PARQUET
LOCATION '/dw/hive/jms_dm.db/external/dm_sham_send_monitor_detail_dt'
TBLPROPERTIES (
    'discover.partitions' = 'false',
    'parquet.column.index.access' = 'true'
);


-----------------------------------------------
CREATE EXTERNAL TABLE jms_dm.dm_sham_send_monitor_detail_dt_mid_update(
  bill_code string COMMENT '运单编号',
  input_time string COMMENT '运单录入时间',
  taking_time string COMMENT '揽收时间',
  network_code string COMMENT '首责任网点code',
  network_name string COMMENT '首责任网点名称',
  network_type string COMMENT '首责任网点类型',
  agent_code string COMMENT '首责任机构所属代理区code',
  agent_name string COMMENT '首责任机构所属代理区',
  fst_duty_type string COMMENT '首责任类型',
  fst_duty_standard string COMMENT '首责任标准',
  start_network_code string COMMENT '始发网点code',
  start_network_name string COMMENT '始发网点名称',
  start_agent_code string COMMENT '始发代理区code',
  start_agent_name string COMMENT '始发代理区',
  start_city_id string COMMENT '始发城市id',
  start_city_desc string COMMENT '始发城市',
  start_provider_id string COMMENT '始发省份id',
  start_provider_desc string COMMENT '始发省份',
  destination_network_code string COMMENT '目的网点code',
  destination_network_name string COMMENT '目的网点',
  destination_agent_code string COMMENT '目的代理区code',
  destination_agent_name string COMMENT '目的代理区',
  destination_city_id string COMMENT '目的城市id',
  destination_city_desc string COMMENT '目的城市',
  destination_provider_id string COMMENT '目的省份id',
  destination_provider_desc string COMMENT '目的省份',
  line_link_code string COMMENT '链路环节code',
  line_link_name string COMMENT '链路环节',
  duty_org_code string COMMENT '责任机构code',
  duty_org_name string COMMENT '责任机构',
  duration_hour string COMMENT '时长',
  deliver_network_code string COMMENT '出仓网点code',
  deliver_network_name string COMMENT '出仓网点',
  deliver_city_id string COMMENT '出仓网点所属城市id',
  deliver_city_desc string COMMENT '出仓网点所属城市',
  deliver_provider_id string COMMENT '出仓网点所属省份id',
  deliver_provider_desc string COMMENT '出仓网点所属省份',
  sign_time string COMMENT '签收时间',
  is_reback string COMMENT '是否退件',
  fst_network_code string COMMENT '首机构code',
  fst_network_name string COMMENT '首机构名称',
  fst_network_opt_time string COMMENT '首机构操作时间',
  ordersource_name string COMMENT '平台名称',
  taking_date date comment '揽收日期'
)
COMMENT '虚假发货监控-明细表-中间表(存儲代码)'
PARTITIONED BY (
  dt string COMMENT'分区日期',
  ordersource_code string COMMENT '平台code')
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  'hdfs://yl-bg-hdp/dw/hive/jms_dm.db/external/dm_sham_send_monitor_detail_dt_mid_update'


